﻿using System;
using System.Collections.Generic;
using System.Text;
using HZ.Supervise.Model;

namespace HZ.Supervise.DAL
{
    public class BuildSql
    {
        private const string _Template1 = @"SELECT {InfoFields} FROM {TableInfo} INNER JOIN (SELECT {GroupFields} FROM View_Supervise  {WHERE1} GROUP BY {GroupByField}) AS B ON {TableAlias}.{GroupByField} = B.{GroupByField} ORDER BY {OrderBy}";
        private const string _Template2 = @"SELECT {GroupFields} FROM View_Supervise  {WHERE1} GROUP BY {GroupByField} ORDER BY {OrderBy}";
        /// <summary>
        /// 增加B前缀
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        private static string AddPrefix(string s)
        {
            string[] array = s.Split(',');
            StringBuilder sb = new StringBuilder();
            bool first = true;
            foreach (var item in array)
            {
                if (first)
                {
                    sb.AppendFormat("B.{0}", item.Trim());
                    first = false;

                }
                else
                {
                    sb.AppendFormat(",B.{0}", item.Trim());
                }
            }
            return sb.ToString();

        }
        /// <summary>
        /// 获取内部查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryobject"></param>
        /// <returns></returns>
        private static string GetInnerSql<T>(QueryObject queryobject) where T : class, IQuery
        {
            // 配送企业单独处理
            if (queryobject.GroupField == GroupByField.CompanyID_PS)
            {
                string sql = "SELECT {GroupFields} FROM View_Supervise  {WHERE1} GROUP BY {GroupByField}";

                sql = sql.Replace("{InfoFields}", queryobject.GroupSelectField.ToString())
                    .Replace("{GroupByField}", "CompanyID_PS")
                    .Replace("{GroupFields}", "CompanyID_PS" + Enum_Helper.GetString(queryobject.GroupSelectField))
                   .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{OrderBy}", queryobject.SortField); ;
                return sql;
            }

            // 地区比较复杂单独处理
            if (queryobject.GroupField == GroupByField.AreaID)
            {
                string sql = "SELECT SubString(AreaID,1,4) +'00' AS AreaID {GroupFields} FROM View_Supervise {WHERE1} GROUP BY SubString(AreaID,1,4)";

                sql = sql.Replace("{InfoFields}", queryobject.GroupSelectField.ToString())
                    .Replace("{GroupFields}", Enum_Helper.GetString(queryobject.GroupSelectField))
                    .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{OrderBy}", queryobject.SortField);

                return sql;
            }
            if (queryobject.GroupField == (GroupByField.AreaID | GroupByField.SortSource) && queryobject.Query.Append.Substring(0, 1).Equals("X"))
            {
                if (queryobject.Query.Append.Length > 1)
                {
                    queryobject.Query.Append = queryobject.Query.Append.Substring(1);
                }
                else
                {
                    queryobject.Query.Append = null;
                }

                string sql = "SELECT {GroupFields} FROM View_Supervise {WHERE1} GROUP BY {GroupByField}";
                return sql.Replace("{GroupFields}", Enum_Helper.GetString(queryobject.GroupField, false) + Enum_Helper.GetString(queryobject.GroupSelectField))
                        .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                        .Replace("{GroupByField}", Enum_Helper.GetString(queryobject.GroupField, true))
                        .Replace("SUBSTRING(AreaID,1,4)", "AreaID");
            }
            string _InnerTempalte = "SELECT {GroupFields} FROM View_Supervise {WHERE1} GROUP BY {GroupByField}";

            return _InnerTempalte.Replace("{GroupFields}", Enum_Helper.GetString(queryobject.GroupField, false) + Enum_Helper.GetString(queryobject.GroupSelectField))
                    .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{GroupByField}", Enum_Helper.GetString(queryobject.GroupField, true))
                    .Replace("SUBSTRING(AreaID,1,4)", "SUBSTRING(AreaID,1,4)+'00'");
            ;

        }

        public static string GetSql<T>(QueryObject queryobject) where T : class, IQuery
        {
            // 混合条件处理
            if (queryobject.GroupField.ToString().Split(',').Length > 1)
            {
                string innersql = GetInnerSql<T>(queryobject);
                StringBuilder sb_selectfield = new StringBuilder();
                StringBuilder sb_tablejoin = new StringBuilder();
                sb_selectfield.AppendFormat("{0}", AddPrefix(queryobject.GroupField.ToString()) + "," + queryobject.GroupSelectField);
                string[] strgroupbyfield = queryobject.GroupField.ToString().Split(',');

                foreach (var item in strgroupbyfield)
                {
                    if (TableInfo.GroupFieldTableInfo.ContainsKey(item.Trim()))
                    {
                        sb_tablejoin.AppendFormat(" INNER JOIN {0} ON {3}.{1} = B.{2}", TableInfo.GroupFieldTableInfo[item.Trim()].Table, item.Trim().Replace("CompanyID_PS", "CompanyID"), item.Trim(), TableInfo.GroupFieldTableInfo[item.Trim()].Alias);
                        sb_selectfield.AppendFormat(",{0}", TableInfo.GroupFieldTableInfo[item.Trim()].Fields);
                    }


                }


                return string.Format("SELECT {0} FROM ({1}) AS B {2} ORDER BY {3}", sb_selectfield.ToString(), innersql, sb_tablejoin, queryobject.SortField);
            }

            // 配送企业单独处理
            if (queryobject.GroupField == GroupByField.CompanyID_PS)
            {
                string sql = "SELECT CompanyName AS CompanyName_PS, CompanyID_PS ,{InfoFields} FROM TB_Company INNER JOIN (SELECT {GroupFields} FROM View_Supervise {WHERE1} GROUP BY {GroupByField}) AS B ON TB_Company.CompanyID = B.{GroupByField} ORDER BY {OrderBy}";

                sql = sql.Replace("{InfoFields}", queryobject.GroupSelectField.ToString())
                    .Replace("{GroupByField}", "CompanyID_PS")
                    .Replace("{GroupFields}", "CompanyID_PS" + Enum_Helper.GetString(queryobject.GroupSelectField))
                   .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{OrderBy}", queryobject.SortField); ;
                return sql;
            }

            // 地区比较复杂单独处理
            if (queryobject.GroupField == GroupByField.AreaID)
            {
                string defaultAreaId = "510000";
                SuperviseQuery query = (SuperviseQuery)queryobject.Query;


                string sql = "";
                if (query.AreaID == defaultAreaId || string.IsNullOrEmpty(query.AreaID))              //若是查看全省，则可以查看到各个地州
                {
                    sql = "SELECT AreaName,B.AreaID,{InfoFields} FROM TB_Area INNER JOIN (SELECT SubString(AreaID,1,4) +'00' AS AreaID {GroupFields} FROM View_Supervise {WHERE1} GROUP BY SubString(AreaID,1,4)) AS B ON TB_Area.AreaID = B.AreaID  ORDER BY {OrderBy}";
                }
                else             //若是各个地州，则可以查看各个县
                {
                    sql = "SELECT AreaName,B.AreaID,{InfoFields} FROM TB_Area INNER JOIN (SELECT AreaID {GroupFields} FROM View_Supervise {WHERE1} GROUP BY AreaID) AS B ON TB_Area.AreaID = B.AreaID  ORDER BY {OrderBy}";
                }

                sql = sql.Replace("{InfoFields}", queryobject.GroupSelectField.ToString())
                    .Replace("{GroupFields}", Enum_Helper.GetString(queryobject.GroupSelectField))
                    .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{OrderBy}", queryobject.SortField); ;

                return sql;
            }

            //加成率单独处理
            if (queryobject.GroupField == GroupByField.AdditionRate)
            {
                SuperviseQuery query = (SuperviseQuery)queryobject.Query;
                TableInfo tbinfo = TableInfo.GroupFieldTableInfo[queryobject.GroupField.ToString()];
                string sql = "SELECT {InfoFields} FROM View_HospitalProcureCatalog INNER JOIN (SELECT {GroupFields} FROM View_Supervise  {WHERE1} GROUP BY ProcureCatalogID) AS B ON View_HospitalProcureCatalog.ProcureCatalogID = B.ProcureCatalogID AND View_HospitalProcureCatalog.HospitalID='{HospitalID}' AND View_HospitalProcureCatalog.Hospital_IsUsing=1  ORDER BY {OrderBy}";
                sql = sql.Replace("{InfoFields}", tbinfo.Fields + ",B.ProcureCatalogID," + queryobject.GroupSelectField.ToString())
                       .Replace("{GroupFields}", "ProcureCatalogID" + Enum_Helper.GetString(queryobject.GroupSelectField))
                       .Replace("{WHERE1}", string.Format("WHERE 1=1{0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                       .Replace("{HospitalID}", query.HospitalID)
                       .Replace("{OrderBy}", queryobject.SortField);
                return sql;
            }


            if ((int)queryobject.GroupField > 0x10)
            {
                return _Template2.Replace("{GroupByField}", Enum_Helper.GetString(queryobject.GroupField, true))
                    .Replace("{GroupFields}", Enum_Helper.GetString(queryobject.GroupField, false) + Enum_Helper.GetString(queryobject.GroupSelectField))
                   .Replace("{WHERE1}", string.Format("WHERE 1=1 {0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{OrderBy}", queryobject.SortField);
            }
            else
            {
                TableInfo tbinfo = TableInfo.GroupFieldTableInfo[queryobject.GroupField.ToString()];
                return _Template1.Replace("{InfoFields}", tbinfo.Fields + ",B." + tbinfo.Name + "," + queryobject.GroupSelectField.ToString())
                    .Replace("{GroupByField}", Enum_Helper.GetString(queryobject.GroupField, true))
                    .Replace("{TableInfo}", tbinfo.Table)
                    .Replace("{TableAlias}", tbinfo.Alias)
                    .Replace("{GroupFields}", Enum_Helper.GetString(queryobject.GroupField, true) + Enum_Helper.GetString(queryobject.GroupSelectField))
                    .Replace("{WHERE1}", string.Format("WHERE 1=1{0}", DAL_Helper.GetWhereCondition(queryobject.Query as T, false)))
                    .Replace("{OrderBy}", queryobject.SortField);
            }
        }
    }
}
